﻿Imports System.Data.SQLite
Imports MySql.Data.MySqlClient
Public Class FrmTest
    Dim _mysql As New ClsSQLhelper
    Dim _sqlite As New ClsSQLite
    Dim Strsql As String
    Dim DT_Patient As New DataTable
    Dim DT As New DataTable
    Dim DT2 As New DataTable
    Dim DS2 As New DataSet
    Dim DA As New SQLiteDataAdapter
    Dim DA2 As New MySqlDataAdapter
    Dim SQLiteConnq As New SQLite.SQLiteConnection
    Dim SQLConnq As New MySql.Data.MySqlClient.MySqlConnection
    Dim cmdBuilder As SQLiteCommandBuilder
    Dim cmdBuilder2 As MySqlCommandBuilder

    Sub Load_Hosxp()
        Try
            Strsql = "select hn,cid,pname,fname,lname,birthday,sex,bloodgrp,fathername,mathername,last_visit,nationality,type_area,occupation,religion,marrystatus,educate,last_update  from patient where hn = '0145279'"
            ' Strsql = "Select * from patient wh"
            DT_Patient = _mysql.GetMYSQLDataTable(Strsql, "patient", True)
            DataGridView1.DataSource = DT_Patient
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Sub Load_Oppp()

        Strsql = "select hn,cid,pname,fname,lname,birthday,sex,bloodgrp,fathername,mathername,last_visit,nationality,type_area,occupation,religion,marrystatus,educate,last_update  from patient"
        SQLConnq.ConnectionString = ClsSQLhelper.MySQLConnectionStr
        DA2 = New MySqlDataAdapter(Strsql, SQLConnq)


        cmdBuilder2 = New MySqlCommandBuilder(DA2)

        DA2.Fill(DS2)
        ' DT = DT_Patient
        '

        DataGridView2.DataSource = DS2.Tables(0)
    End Sub
    Sub load_Sqlite()


        Strsql = "select hn,cid,pname,fname,lname,birthday,sex,bloodgrp,fathername,mathername,last_visit,nationality,type_area,occupation,religion,marrystatus,educate,last_update  from patient"
        SQLiteConnq.ConnectionString = ClsSQLite.SQLiteConnectionStr
        DA = New SQLiteDataAdapter(Strsql, SQLiteConnq)


        cmdBuilder = New SQLiteCommandBuilder(DA)

        DA.Fill(DT)
        ' DT = DT_Patient
        '

        DataGridView3.DataSource = DT
    End Sub
    Private Sub BtnPullHOSxP_Click(sender As System.Object, e As System.EventArgs) Handles BtnPullHOSxP.Click
      
    End Sub

    Private Sub BtnInportSQLite_Click(sender As System.Object, e As System.EventArgs) Handles BtnInportSQLite.Click
        Dim dr As DataRow
        For Each dr In DT_Patient.Rows

            'DS2.Tables(0).ImportRow(dr)
            Dim DsNewRow As DataRow
            DsNewRow = DT.NewRow

            For i = 0 To DT_Patient.Columns.Count - 1

                DsNewRow.Item(i) = dr.Item(i)
            Next


            DT.Rows.Add(DsNewRow)

        Next

        DA.Update(DT)
        '     SQLConnq.Close()
        DataGridView3.DataSource = DT


    End Sub

    Private Sub FrmTest_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Load_Hosxp()
        load_Sqlite()
        Load_Oppp()
    End Sub

    Private Sub BtnRefrehOPPP_Click(sender As System.Object, e As System.EventArgs) Handles BtnRefrehOPPP.Click
        Load_Oppp()
    End Sub

    Private Sub BtnRefreshSQLite_Click(sender As System.Object, e As System.EventArgs) Handles BtnRefreshSQLite.Click
        load_Sqlite()
    End Sub

    Private Sub BtnImportOPPP_Click(sender As System.Object, e As System.EventArgs) Handles BtnImportOPPP.Click
        Dim dr As DataRow
        For Each dr In DT_Patient.Rows

            'DS2.Tables(0).ImportRow(dr)
            Dim DsNewRow As DataRow
            DsNewRow = DS2.Tables(0).NewRow

            For i = 0 To DT_Patient.Columns.Count - 1

                DsNewRow.Item(i) = dr.Item(i)
            Next


            DS2.Tables(0).Rows.Add(DsNewRow)

        Next

        DA2.Update(DS2.Tables(0))
        '     SQLConnq.Close()
        DataGridView2.DataSource = DT2

    End Sub

    Private Sub BtnUpdateOPPP_Click(sender As System.Object, e As System.EventArgs) Handles BtnUpdateOPPP.Click
        DA2.Update(DS2.Tables(0))
        '     SQLConnq.Close()
        DataGridView2.DataSource = DS2.Tables(0)
    End Sub
End Class